package edu.hhu.dynamic.table;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class QueryExcel {
	public void writeExcel(String filename,String tablename,String nodeStr,String fieldStr
			,String s_date,String e_date)throws IOException{
		//创建新的Excel工作薄
        HSSFWorkbook workbook=new HSSFWorkbook();
        //在Excel中新建一张工作表，起名为jsp
        HSSFSheet sheet=workbook.createSheet("数据导出1");
        
        TableDao tableDao=new TableDao();
        //表ge 标题处理
        head(sheet,tableDao,fieldStr);
        //表数据
        String sql=tableDao.getExcelSql(tablename,nodeStr, fieldStr, s_date, e_date);
//        System.out.println(sql);
        List<Record> data = Db.use("mysql").find(sql);
        //System.out.println(data);
        String[] fields=fieldStr.split(",");
//        System.out.println(fields.length);
		int i=1;
		int num=1;
		for (Record record : data) {
			Map temp=record.getColumns();
			
			//创建第一行
	        HSSFRow row=sheet.createRow(i);
	        HSSFCell cell0=row.createCell(0);
	        cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
	        cell0.setCellValue(temp.get("WELLNAME").toString());
	        HSSFCell cell1=row.createCell(1);
	        cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
	        cell1.setCellValue(temp.get("SCRQ").toString());
	        
	        
	        for(int j=0;j<fields.length;j++){
	        	//创建第一列
	            HSSFCell cell=row.createCell(j+2);
	          //定义单元格为字符串类型
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	            //在单元格输入一些     内容
	           
	            if(temp.get(fields[j])==null){
	            	cell.setCellValue("");
	            }else{
	            	cell.setCellValue(temp.get(fields[j]).toString());
	            }
	            
	        }
			i++;
			if(i%65530==0){
				num++;
				sheet=workbook.createSheet("数据导出"+num);
				i=0;
			}
		}
		
		
        //新建输出流
        FileOutputStream fout=new FileOutputStream(filename);
        //存盘
        workbook.write(fout);

        fout.flush();
        //结束关闭
        fout.close();
//        System.out.println("sq123");
		
	}
	
	public void head(HSSFSheet sheet,TableDao tableDao,String fieldStr){
		//表标题
        List<Map> heads=tableDao.getHaeder();
        Map head=new HashMap();
        for(Map map:heads){
        	head.put(map.get("fieldcode"),map.get("fieldname"));
        }
        
        String[] fields=fieldStr.split(",");

        //创建第一行
        HSSFRow row0=sheet.createRow(0);
        HSSFCell cell0=row0.createCell(0);
        cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell0.setCellValue("井号");
        HSSFCell cell1=row0.createCell(1);
        cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell1.setCellValue("生产日期");
        
        
        for(int i=0;i<fields.length;i++){
        	//创建第一列
            HSSFCell cell=row0.createCell(i+2);
          //定义单元格为字符串类型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //在单元格输入一些     内容
            cell.setCellValue(head.get(fields[i]).toString());
        }
		
	}
	
	 public void writeExcel(String filename)throws IOException {
	        //创建新的Excel工作薄
	        HSSFWorkbook workbook=new HSSFWorkbook();
	        //在Excel中新建一张工作表，起名为jsp
	        HSSFSheet sheet=workbook.createSheet("Jsp");
	        //创建第一行
	        HSSFRow row=sheet.createRow(0);
	        //创建第一列
	        HSSFCell cell=row.createCell(0);
	        //定义单元格为字符串类型
	        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	        //在单元格输入一些     内容
	        cell.setCellValue(new Date());
	        //        新建输出流
	        FileOutputStream fout=new FileOutputStream(filename);
	        //存盘
	        workbook.write(fout);

	        fout.flush();
	        //结束关闭
	        fout.close();
	    }

}
